package com.jnu.ilearn.utils;

import android.os.Handler;

import com.jnu.ilearn.bean.Seat;
import com.jnu.ilearn.bean.SeatItem;
import com.jnu.ilearn.bean.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

public class DataLoader {
    List<User> users=new ArrayList<>();
    List<SeatItem> seats=new ArrayList<>();

    public void LoadUser(Handler handler) {
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    Class.forName("com.mysql.jdbc.Driver");
                    String dburl = "jdbc:mysql://120.24.40.131:3306/iLearn?&useSSL=false&serverTimezone=UTC";
                    String sql = "SELECT * FROM user";
                    Connection conn = DriverManager.getConnection(dburl, "root", "19_Learn");
                    Statement stmt = conn.createStatement();
                    ResultSet rst = stmt.executeQuery(sql);
                    while (rst.next()) {
                        users.add(new User( rst.getString(2), rst.getString(3), rst.getString(4),
                                rst.getString(5),rst.getInt(6)));
                    }
                    DBUtils.disconnect(conn);
                    handler.sendEmptyMessage(1);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }).start();
    }

    public void LoadSeat(Handler handler) {
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    Class.forName("com.mysql.jdbc.Driver");
                    String dburl = "jdbc:mysql://120.24.40.131:3306/iLearn?&useSSL=false&serverTimezone=UTC";
                    String sql = "SELECT * FROM seat";
                    Connection conn = DriverManager.getConnection(dburl, "root", "19_Learn");
                    Statement stmt = conn.createStatement();
                    ResultSet rst = stmt.executeQuery(sql);
                    while (rst.next()) {
                        Timestamp timestamp = rst.getTimestamp(4);
                        Calendar calendar=new GregorianCalendar();
                        if(timestamp!=null){
                            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            String dateText=dateFormat.format(timestamp);
                            Date date=dateFormat.parse(dateText);
                            calendar.setTime(date);
                        }
                        seats.add(new SeatItem(rst.getBoolean(1),rst.getString(5),
                                calendar,rst.getString(3),rst.getInt(2)));
                    }
                    DBUtils.disconnect(conn);
                    handler.sendEmptyMessage(1);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }).start();
    }
    public List<User> GetUser(){
        return users;
    }
    public List<SeatItem> GetSeat(){
        return seats;
    }
}
